package com.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.bean.InstrumentBean;
import com.bean.RecordBean;
import com.bean.UserBean;
import com.db.JdbcUtil;
import com.exception.DBException;
import com.exception.StatisticException;

public class StatisticDao
{
	private Connection con = null;
	private PreparedStatement ps = null;
	private ResultSet rs = null;

	public ArrayList<RecordBean> getTopTen() throws DBException,
			StatisticException
	{

		String sql = "select ins.barcode,ins.name,ins.price,ins.date,degree from (SELECT barcode,count(barcode) as degree FROM lab_record group by barcode) as borr join (select name,ifin,price,date,barcode from lab_instrument) as ins on borr.barcode=ins.barcode order by borr.degree desc limit 10";
		con = JdbcUtil.getConnection();
		RecordBean rb = null;
		ArrayList<RecordBean> list = new ArrayList<RecordBean>();
		try
		{
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next())
			{
				rb = new RecordBean();
				rb.setBarcode(rs.getString(1));
				rb.setInsName(rs.getString(2));
				rb.setPrice(rs.getString(3));
				rb.setCreateDate(rs.getDate(4));
				rb.setDegree(rs.getInt(5));
				list.add(rb);
			}

		}
		catch (SQLException e)
		{
			throw new StatisticException("Fail to get top ten instruments");
		}
		finally
		{
			JdbcUtil.free(rs, ps, con);
		}
		return list;
	}

	public ArrayList<InstrumentBean> getIn() throws DBException,
			StatisticException
	{

		String sql = "select barcode,name,price,date,ifin,remark from lab_instrument where ifin='yes'";
		con = JdbcUtil.getConnection();
		ArrayList<InstrumentBean> list = new ArrayList<InstrumentBean>();
		InstrumentBean ins = null;
		try
		{
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next())
			{
				ins = new InstrumentBean();
				ins.setBarcode(rs.getString(1));
				ins.setName(rs.getString(2));
				ins.setPrice(rs.getString(3));
				ins.setDate(rs.getDate(4));
				ins.setIfin(rs.getString(5));
				ins.setRemark(rs.getString(6));
				list.add(ins);
			}

		}
		catch (SQLException e)
		{
			throw new StatisticException(
					"Fail to get instruments that are in the lab");
		}
		finally
		{
			JdbcUtil.free(rs, ps, con);
		}
		return list;
	}

	public ArrayList<RecordBean> getOut() throws DBException,
			StatisticException
	{

		String sql = "select d.barcode,d.name,d.price,d.ifin,a.userID,a.firstname,a.lastname,a.email,c.borrowDate,c.sbackDate from lab_user a join lab_record c on a.userID = c.userID join lab_instrument d on d.barcode = c.barcode where c.backDate is null";
		con = JdbcUtil.getConnection();
		RecordBean rb = null;
		ArrayList<RecordBean> list = new ArrayList<RecordBean>();
		try
		{
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next())
			{
				rb = new RecordBean();
				rb.setBarcode(rs.getString(1));
				rb.setInsName(rs.getString(2));
				rb.setPrice(rs.getString(3));
				rb.setIfIn(rs.getString(4));
				rb.setUserID(rs.getString(5));
				rb.setFirstname(rs.getString(6));
				rb.setLastname(rs.getString(7));
				rb.setEmail(rs.getString(8));
				rb.setBorrowDate(rs.getDate(9));
				rb.setSBackDate(rs.getDate(10));
				list.add(rb);
			}

		}
		catch (SQLException e)
		{
			throw new StatisticException("Fail to get top ten instruments");
		}
		finally
		{
			JdbcUtil.free(rs, ps, con);
		}
		return list;
	}

	public ArrayList<InstrumentBean> getAllIns() throws DBException,
			StatisticException

	{
		String sql = "select * from lab_instrument";
		ArrayList<InstrumentBean> list = new ArrayList<InstrumentBean>();
		InstrumentBean inst = null;
		con = JdbcUtil.getConnection();
		try
		{
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next())
			{
				inst = new InstrumentBean();
				inst.setBarcode(rs.getString("barcode"));
				inst.setName(rs.getString("name"));
				inst.setIfin(rs.getString("ifin"));
				inst.setRemark(rs.getString("remark"));
				inst.setPrice(rs.getString("price"));
				inst.setDate(rs.getDate("date"));
				list.add(inst);
			}
		}
		catch (Exception e)
		{
			throw new StatisticException("fail to get all instruments!");
		}
		finally
		{
			JdbcUtil.free(rs, ps, con);
		}
		return list;
	}

	public ArrayList<UserBean> getAllUsers() throws DBException,
			StatisticException
	{
		String sql = "select * from lab_user";
		con = JdbcUtil.getConnection();
		ArrayList<UserBean> list = new ArrayList<UserBean>();
		UserBean userBean = null;
		try
		{
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next())
			{
				userBean = new UserBean();
				userBean.setUserID(rs.getString("userID"));
				userBean.setFirstname(rs.getString("firstname"));
				userBean.setLastname(rs.getString("lastname"));
				userBean.setGender(rs.getString("gender"));
				userBean.setAge(rs.getString("age"));
				userBean.setEmail(rs.getString("email"));
				userBean.setPhone(rs.getString("phone"));
				userBean.setRemark(rs.getString("remark"));
				userBean.setDate(rs.getDate("date"));
				list.add(userBean);
			}
		}
		catch (Exception e)
		{
			throw new StatisticException("Fail to get all users ");
		}
		finally
		{
			JdbcUtil.free(rs, ps, con);
		}
		return list;
	}
}
